/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var TgOrgRModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`org_id` bigint(20) unsigned NOT NULL COMMENT '公司组织id',
  //`user_id` bigint(20) NOT NULL COMMENT '用户id',
  //`role` int(2) NOT NULL DEFAULT '0' COMMENT '用户角色: 1管理员, 2普通用户',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL DEFAULT '',
  //`del_flg` int(1) NOT NULL DEFAULT '0'
};
TgOrgRModel.prototype = new ItbBaseModel();

// 取得表名
TgOrgRModel.prototype.getTableName = function(){
    return 'tg_org_r';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
TgOrgRModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'org_id='+this.org_id;
}

//------------------------------------------------------------------
/**
 * 获取指定组织的待审核模版
 * @param bForCnt
 * @param orgId
 * @param publish
 * @param states
 * @param designKind
 * @param skipCnt
 * @param pageSize
 * @param callback
 * @returns {string}
 */
function sql_getDesignByOrg(bForCnt, orgId, publish, states, designKind, skipCnt, pageSize, callback){
    // 静态模版
    var sql_10 = ' SELECT t.design_id, t.map_id, t.tg_id, pg.thumb, t.publish, '+
        ' t.design_name, t.user_id, t.state, t.update_time, '+
        ' 10 AS design_kind '+
        ' FROM TG_DESIGN_DOC t, TG_ORG o, TG_ORG_R r, TG_DESIGN_PAGE pg '+
        ' WHERE o.del_flg=0 AND o.state=4 '+// 合法组织
        ' AND o.org_id='+orgId+
        ' AND r.del_flg=0 '+
        ' AND o.org_id=r.org_id '+
        ' AND t.del_flg=0 AND t.user_id=r.user_id '+// 该组织用户
        ' AND pg.del_flg=0 AND pg.design_id=t.design_id AND pg.page_index=0 ';
    if(Number(publish||'0')>=0) {
        sql_10 += ' AND t.publish ='+publish;
    }
    if(states) {
        sql_10 += ' AND t.state IN ('+states+') ';
    }

    // 视频模版
    var sql_20 = ' SELECT vd.design_id, null AS map_id, null AS tg_id, vd.theme_thumb AS thumb, vd.publish, '+
        ' vd.design_name, vd.user_id, vd.state, vd.update_time, '+
        ' 20 AS design_kind '+
        ' FROM VIDEO_DESIGN_DOC vd, TG_ORG o, TG_ORG_R r '+
        ' WHERE o.del_flg=0 AND o.state=4 '+// 合法组织
        ' AND o.org_id='+orgId+
        ' AND r.del_flg=0 '+
        ' AND o.org_id=r.org_id '+
        ' AND vd.del_flg=0 AND vd.user_id=r.user_id ';// 该组织用户
    if(Number(publish||'0')>=0) {
        sql_20 += ' AND vd.publish ='+publish;
    }
    if(states) {
        sql_20 += ' AND vd.state IN ('+states+') ';
    }

    // 主sql
    var sql = '';
    if(bForCnt) {
        sql += ' SELECT COUNT(*) AS cnt ';
    } else {
        sql += ' SELECT xx.*, IFNULL(h.content,"") AS ng_msg ' ;
    }

    if(designKind==10) {
        // 仅静态设计
        sql += ' FROM ( '+sql_10+' ) xx ';

    } else if(designKind==20) {
        // 仅视频设计
        sql += ' FROM ( '+sql_20+' ) xx ';

    } else {
        // 全部
        sql += ' FROM ( '+sql_10+' UNION ALL '+sql_20+' ) xx ';
    }

    if(!bForCnt) {
        sql += ' LEFT JOIN tg_review_his h ON h.del_flg=0 AND h.design_id=xx.design_id '+
            ' AND h.state=xx.state AND h.kind=xx.design_kind '+
            ' AND h.his_id IN (SELECT MAX(h2.his_id) FROM tg_review_his h2 WHERE h2.del_flg=0 GROUP BY h2.kind, h2.design_id, h2.state) ';// 取最新一条
        sql += ' ORDER BY xx.state, xx.update_time DESC ';

        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " LIMIT " + Number(skipCnt) + "," + Number(pageSize);
        }
    }

    return sql;
}

/* 获取指定组织的待审核模版
 * 参数:org_id
 * 参数:callback:回调
 * */
TgOrgRModel.prototype.getDesignByOrg = function(callback, skipCnt, pageSize){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        //var sql = ' SELECT tt.*, ifnull(h.content,"") AS ng_msg '+
        //    ' FROM ('+
        //    ' SELECT t.design_id, t.map_id, t.tg_id, pg.thumb, t.publish, '+
        //    ' t.design_name, t.user_id, t.state, t.update_time '+
        //    ' FROM TG_DESIGN_DOC t, TG_ORG o, TG_ORG_R r, TG_DESIGN_PAGE pg '+
        //    ' WHERE o.del_flg=0 AND o.state=4 '+// 合法组织
        //    ' AND o.org_id= '+THIS.org_id+
        //    ' AND r.del_flg=0 '+
        //    ' AND o.org_id=r.org_id '+
        //    ' AND t.del_flg=0 AND t.user_id=r.user_id '+// 该组织用户
        //    ' AND pg.del_flg=0 AND pg.design_id=t.design_id AND pg.page_index=0 ';
        //if(Number(THIS.publish||'0')>=0) {
        //    sql += ' AND t.publish ='+THIS.publish;
        //}
        //if(THIS.state) {
        //    sql += ' AND t.state IN ('+THIS.state+') ';
        //}
        //sql+=' )tt '+
        //    ' LEFT JOIN TG_REVIEW_HIS h ON h.del_flg=0 AND h.design_id=tt.design_id AND h.state=tt.state '+
        //    ' AND h.his_id IN (SELECT MAX(h2.his_id) FROM tg_review_his h2 WHERE h2.del_flg=0 GROUP BY h2.design_id, h2.state) '+// 取最新一条
        //    ' ORDER BY tt.state, tt.update_time DESC ';
        ////sql += ' ORDER BY t.order, t.design_id DESC ';
        //
        //if (typeof skipCnt!='undefined' && skipCnt != null
        //    && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
        //    sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        //}

        var sql = sql_getDesignByOrg(false, THIS.org_id, THIS.publish, THIS.state, THIS.design_kind,
                skipCnt, pageSize);

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            callback(error, result);
        });
    });
};

TgOrgRModel.prototype.getDesignCntByOrg = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        //var sql =
        //    ' SELECT COUNT(t.design_id) AS cnt '+
        //    ' FROM TG_DESIGN_DOC t, TG_ORG o, TG_ORG_R r, TG_DESIGN_PAGE pg '+
        //    ' WHERE o.del_flg=0 AND o.state=4 '+// 合法组织
        //    ' AND o.org_id= '+THIS.org_id+
        //    ' AND r.del_flg=0 '+
        //    ' AND o.org_id=r.org_id '+
        //    ' AND t.del_flg=0 AND t.user_id=r.user_id '+// 该组织用户
        //    ' AND pg.del_flg=0 AND pg.design_id=t.design_id AND pg.page_index=0 ';
        //if(Number(THIS.publish||'0')>=0) {
        //    sql += ' AND t.publish ='+THIS.publish;
        //}
        //if(THIS.state) {
        //    sql += ' AND t.state IN ('+THIS.state+') ';
        //}

        var sql = sql_getDesignByOrg(true, THIS.org_id, THIS.publish, THIS.state, THIS.design_kind);

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0) {
                callback(error, result[0].cnt);
            } else {
                callback(error, 0);
            }
        });
    });
};
//------------------------------------------------------------------

//------------------------------------------------------------------
/* 获取指定组织的待审核模版
 * 参数:user_id
 * 参数:callback:回调
 * */
TgOrgRModel.prototype.getNewDesignByAdmin = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT t.design_id, t.map_id, t.tg_id, pg.thumb, '+
            ' t.design_name, t.user_id '+
            ' FROM TG_DESIGN_DOC t, TG_ORG o, TG_ORG_R r, TG_DESIGN_PAGE pg  '+
            ' WHERE o.del_flg=0 AND o.state=4 '+// 合法组织
            ' AND o.org_id IN ' +
            ' (SELECT x.org_id FROM TG_ORG_R x '+
            '   WHERE x.del_flg=0 AND x.role=10 AND x.user_id='+THIS.user_id+')'+// 管理员所在组织
            ' AND r.del_flg=0 AND r.role=20 '+// 普通用户
            ' AND o.org_id=r.org_id '+
            ' AND t.del_flg=0 AND t.user_id=r.user_id '+// 该组织用户
            ' AND t.state=20 '+// 待审状态
            ' AND pg.del_flg=0 AND pg.design_id=t.design_id AND pg.page_index=0 '+
            ' ORDER BY t.order, t.design_id DESC ';

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            callback(error, result);
        });
    });
};

/**
 * 将用户和组织的关系置为无效
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param orgId     组织标识
 * @param userId    用户标识
 * @param callback  回调
 *          err:     删除失败
 */
TgOrgRModel.prototype.invalidateUserOrgRelation = function (orgId, userId, callback) {
    pool.pool.getConnection(function(getConErr, connection) {
        if(getConErr) {
            console.log('ItbUserModel.prototype.getDesignPlatformUserInfo getConErr: ' + getConErr);
            return callback(getConErr, null);
        }

        var sql =
            ' UPDATE tg_org_r tor ' +
            ' SET tor.state = 20 ' +
            ' WHERE tor.user_id = ' + userId +
            '   AND tor.org_id = ' + orgId +
            '   AND tor.del_flg = 0';
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            connection.release();
            if(queryErr) {
                console.log('ItbUserModel.prototype.getDesignPlatformUserInfo queryErr: ' + queryErr);
                return callback(queryErr)
            }
            if (!results || results.length <= 0) {
                return callback(queryErr, null);
            }
            return callback(queryErr, results[0]);
        });
    });

};

/**
 * 获取组织下用户列表 sql 语句
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param {Number} bForCnt  获取个数语句, 获取列表语句
 * @param {Object} options 请求参数
 * @param {Number} options.orgId     组织 id
 * @param {Array} options.roles     角色数组
 * @param {Array} [options.userNames] 用户名限定数组
 * @param {Number} options.pageNum  页码
 * @param {Number} options.pageSize  页容量
 * @return {String} sql  对应的 sql
 */
function sql_getRolesUserList(bForCnt, options) {

    var sql = '';
    if(bForCnt) {
        sql += ' SELECT COUNT(0) AS count '
    } else {
        sql += ' SELECT iu.user_id AS userId, ' +
            '        iu.user_name AS userName, ' +
            '        iu.nickname AS nickName, ' +
            '        DATE_FORMAT(iu.create_time,"%Y-%m-%d %H:%i:%s") AS createTime, ' +
            '        to.org_id AS orgId, ' +
            '        tor.role ';
    }

    sql +=
        ' FROM tg_org `to`, ' +
        '      tg_org_r tor, ' +
        '      itb_user iu ' +
        ' WHERE to.org_id = ' + options.orgId +
        '   AND tor.org_id = to.org_id ' +
        '   AND tor.state = 10 ' +
        '   AND tor.role in ( ' + options.roles.join(',') + ') ' +
        '   AND iu.user_id = tor.user_id ' +
        '   AND iu.user_type = 0 ' +
        '   AND to.del_flg = 0 ' +
        '   AND tor.del_flg = 0 ' +
        '   AND iu.del_flg = 0 ';
    if(options.userNames && options.userNames.length > 0) {
        var userNamesJsonString = JSON.stringify(options.userNames);
        var userNamesLimit = userNamesJsonString.substr(1, userNamesJsonString.length-2);
        sql += '   AND iu.user_name in ( ' + userNamesLimit + ') ';
    }


    if(!bForCnt && options.pageNum >=0 && options.pageSize >= 0){
        var skipCnt = options.pageNum * options.pageSize;
        sql += " LIMIT " + Number(skipCnt) + "," + Number(options.pageSize);
    }
    return sql;
}

/**
 * 获取组织下用户列表
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param {Object} options  请求参数
 * @param {Number} options.orgId     组织 id
 * @param {Array} options.roles     角色数组
 * @param {Array} [options.userNames] 昵称限定数组
 * @param {Number} options.pageNum   页码
 * @param {Number} options.pageSize  页容量
 * @param {Function} callback  回调函数
 */
TgOrgRModel.prototype.getRolesUserList = function (options, callback) {
    pool.pool.getConnection(function(getConErr, connection) {
        if(getConErr) {
            console.log('ItbUserModel.prototype.getRolesUserList getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getRolesUserList(false, options);
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            connection.release();
            if(queryErr) {
                console.log('ItbUserModel.prototype.getRolesUserList queryErr: ' + queryErr);
                return callback(queryErr)
            }
            return callback(queryErr, results);
        });
    });
};

/**
 * 获取组织下用户列表
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param {Object} options 请求参数
 * @param {Number} options.orgId     组织 id
 * @param {Array} options.roles     角色数组
 * @param {Array} [options.userNames]     角色数组
 * @param {Function} callback  回调函数
 */
TgOrgRModel.prototype.getRolesUserListCnt = function (options, callback) {
    pool.pool.getConnection(function(getConErr, connection) {
        if(getConErr) {
            console.log('ItbUserModel.prototype.getRolesUserList getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getRolesUserList(true, options);
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            connection.release();
            if(queryErr || !results || results.length <=0) {
                console.log('ItbUserModel.prototype.getRolesUserList queryErr: ' + queryErr);
                return callback(queryErr)
            }
            return callback(queryErr, results[0].count);
        });
    });
};

//------------------------------------------------------------------


// 导出
module.exports = TgOrgRModel;